using System;
using Microsoft.Office.Interop.Excel;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
namespace orienta.Utils
{
    /// <summary>
    /// Class to Export the Data to Excel sheet. Input must be a IList colection
    /// </summary>
    /// <typeparam name="T">Entity</typeparam>
    public class ExcelAppExportor
    {
        /// <summary>
        /// Constructor
        /// </summary>
        public ExcelAppExportor()
        {
            TemplateFileName = string.Empty;
            IsExportIncludesHeader = true;
            ExportSheetName = "Export";
        }
        /// <summary>
        /// Holds the Data
        /// </summary>
//mvalle                private IList<T> ListData;
        private IList<dynamic> ListData;
        private String[] Columns;
        private String[] Headers = null;

        /// <summary>
        /// Holds the Export Columns
        /// </summary>
        private IList<ExportColumn> ExportColumns;

        /// <summary>
        /// Add the export column
        /// </summary>
        /// <param name="ExportColumn">ExportColumn</param>
        public void AddExportColumn(ExportColumn ExportColumn)
        {
            if (ExportColumns == null) ExportColumns = new List<ExportColumn>();
            ExportColumns.Add(ExportColumn);
        }
        /// <summary>
        /// List contains the list of entity object which are export to excel
        /// </summary>
      /*  public IList<T> BindDataList
        {
            get { return ListData; }
            set { ListData = value; }
        }*/
        //mvalle
        public IList<dynamic> BindDataList
        {
            get { return ListData; }
            set { ListData = value; }
        }

        public String [] ListColumns
        {
            get { return Columns; }
            set { Columns = value; }
        }
        public String[] ListHeader
        {
            get { return Headers; }
            set { Headers = value; }
        }

        /// <summary>
        /// File Name of the Export output file
        /// </summary>
        public string ExportFileName { get; set; }

        /// <summary>
        /// Template File Name - Using Template file to Export
        /// </summary>
        public string TemplateFileName { get; set; }

        public string[] Etiquetas{ get; set; }


        /// <summary>
        /// Sheet name to Export the data
        /// </summary>
        public string ExportSheetName { get; set; }

        /// <summary>
        /// Is the header data needs to be exported
        /// </summary>
        public bool IsExportIncludesHeader { get; set; }


        private Boolean esEtiqueta( IDictionary<String, object> col) { 
        

            foreach ( String etiqueta in Etiquetas){
                Object valor = col[etiqueta];
                if (valor.Equals("") || valor.Equals("ztotal"))
                    return false;
            }
            return true;

        }

        private Boolean esEtiquetaTotal(IDictionary<String, object> col)
        {


            foreach (String etiqueta in Etiquetas)
            {
                Object valor = col[etiqueta];
                if (!valor.Equals("") && !valor.Equals("ztotal"))
                    return false;
            }
            return true;

        }

        public void Export()
        {

            String version = "";
            try
            {
                #region Filling Export Columns
                // Check the columns to export is mentioned, if not
                if (ExportColumns == null)
                {
                    // Create an entity object. If the list count == 0 ??? - needs to be handled from client
                   //mvalle T tEntity = ListData[0];

                    // Export the columns to export from the property name
                    ExportColumns = new List<ExportColumn>();
                    //mvalle foreach (System.Reflection.PropertyInfo propertyInfo in tEntity.GetType().GetProperties())
                    //mvalle                        ExportColumns.Add(new ExportColumn(propertyInfo.Name, propertyInfo.Name));
                }
                #endregion

                // Create excel application
                //Application ExcelApp = new ApplicationClass();
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
              /*  Type t = Type.GetTypeFromProgID("Excel.Application");
object objExcel = System.Activator.CreateInstance(t);*/
                version = ExcelApp.Path;
                Workbook workbook;
                Sheets sheets;
                Worksheet worksheet;

                // Is Export needs to be exported to a Template file
                if (TemplateFileName != string.Empty)
                {
                    #region Load the Template file
                    // Load the work book
                    workbook = ExcelApp.Workbooks.Open(TemplateFileName, 0, false, 5, "", "", false,
                        XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                    sheets = workbook.Sheets;
                    worksheet = (Worksheet)sheets.get_Item(1); // To avoid unassigned variable error

                    bool IsWorkSheetFound = false;

                    //Check is there any worksheet with the name provided. If yes, clear all data inside to fill new data
                    for (int intSheetIndex = 1; intSheetIndex <= sheets.Count; intSheetIndex++)
                    {
                        worksheet = (Worksheet)sheets.get_Item(intSheetIndex);
                        if (worksheet.Name.ToString().Equals(ExportSheetName))
                        {
                            IsWorkSheetFound = true;
                            break;
                        }
                    }

                    // If No work sheet found, add it at the last
                    if (!IsWorkSheetFound)
                    {
                        worksheet = (Worksheet)workbook.Sheets.Add(
                            Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
                            Type.Missing, Type.Missing);
                        worksheet.Name = ExportSheetName;
                    }
                    #endregion
                }
                else
                {
                    #region Crate the Template File
                    // Adding new work book
                    workbook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

                    sheets = workbook.Sheets;

                    worksheet = (Worksheet)sheets.get_Item(1);

                    worksheet.Name = ExportSheetName;
                    #endregion
                }
                int intCol = 0;

                #region Populating the Columns

                int num = BindDataList.Count;
                if (num > 0)
                {
                    IEnumerator<dynamic> enumerator = ListData.GetEnumerator();

                    enumerator.MoveNext();
                    dynamic current = enumerator.Current;
                    dynamic columna = new System.Dynamic.ExpandoObject();
                    var col1 = columna as IDictionary<String, object>;
                    col1 = current;
                    ICollection<String> keys = col1.Keys;
                    int icolumna = 2;
                    foreach (String key in keys) {
                        //int numColumna =  
                        this.AddExportColumn(new ExportColumn(key, key, 9, icolumna));
                        icolumna++;
                    }

                    if (IsExportIncludesHeader == true) {
                        icolumna = 2;
                        if (Headers == null)
                            foreach (String key in keys)
                            {
                                Range range = (Range)worksheet.Cells[8, icolumna];
                                range.Select();
                                range.Value2 = key.ToUpper();
                                range.Columns.EntireColumn.AutoFit();
                                range.Font.Bold = true;
                                icolumna++;
                            }
                        else {
                            foreach (String key in Headers)
                            {
                                Range range = (Range)worksheet.Cells[8, icolumna];
                                range.Select();
                                range.Value2 = key.ToUpper();
                                range.Columns.EntireColumn.AutoFit();
                                range.Font.Bold = true;
                                icolumna++;
                            }
                        
                        }
                    }
                    
                }

                #endregion

                #region Populating the Header



                // If the header needs to exported. In templated files, normally we will have the headings
                if (IsExportIncludesHeader == true)
                {
                    // Exporting Header
                   /*
                    foreach (ExportColumn exportColumn in ExportColumns)
                    {
                        Range range = (Range)worksheet.Cells[8, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                        range.Select();
                        range.Value2 = exportColumn.HeaderText.ToString();
                        range.Columns.EntireColumn.AutoFit();
                        range.Font.Bold = true;
                    }*/

                }
                #endregion

               
                foreach (dynamic tEntity in BindDataList)
                {
                    intCol = 0;

                    dynamic columna = new System.Dynamic.ExpandoObject();
                    var col = columna as IDictionary<String, object>;
                    col = tEntity;

                    if (esEtiqueta(col))
                    {

                        foreach (ExportColumn exportColumn in ExportColumns)
                        {
                            Object valor = col[exportColumn.ColumnName];

                            Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                            range.Select();
                            //mvalle range.Value2 = tEntity.GetType().GetProperty(exportColumn.ColumnName.ToString()).GetValue(tEntity, null).ToString();


                            range.Value2 = valor;

                            range.Columns.EntireColumn.AutoFit();
                        }
                    }

                    if (esEtiquetaTotal(col))
                    {
                        Boolean total=false;
                        foreach (ExportColumn exportColumn in ExportColumns)
                        {
                            Object valor = col[exportColumn.ColumnName];

                            if ((valor.GetType().ToString().Equals("System.String") && (valor.ToString().Equals("") || valor.ToString().Equals("ztotal"))) && !total)
                            {
                                valor = "TOTAL";
                                total = true;
                            }

                            Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                            range.Select();
                            range.Value2 = valor;
                            range.Columns.EntireColumn.AutoFit();
                        }
                    }

                    
                }

                intCol = 0;
                foreach (ExportColumn exportColumn in ExportColumns)
                {
                    Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                    if (exportColumn.ValueFormat != string.Empty)
                        range.Columns.EntireColumn.NumberFormat = exportColumn.ValueFormat;
                }
                try
                {
                    // Save the workbook with saving option
                    workbook.Close(true, ExportFileName, Type.Missing);
                    ExcelApp.UserControl = false;
                    ExcelApp.Quit();
                    ExcelApp = null;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            catch (Exception ex)
            {
                throw ex;// new Exception("version:" + version, ex);
            }
            finally
            {
                Process[] ps = Process.GetProcesses();
                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                        p.Kill();
                }
            }
        }
    }
}